package com.softer.chengxin.corp.dao.provider;

import com.softer.chengxin.common.domin.Page;
import org.apache.commons.lang3.StringUtils;

import java.util.HashMap;

/**
 * @author wangxinrui
 * @date 2018/10/12
 */
public class ManagerDaoProvider {

    /**
     * 查询企业从业人员信息 条件查询
     */
    public String findCYRY(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String qyId = (String) map.get("qyId");
        Page page = (Page) map.get("page");
        String name = (String) map.get("name");
        String depId = (String) map.get("depId");

        sql.append("select @rowno:=@rowno+1 as row,p.id as id,DATE_FORMAT(p.gmt_create, '%Y-%m-%d') as gmtCreate,DATE_FORMAT(p.gmt_modified, '%Y-%m-%d') as gmtModified,p.person_id as personId,p.project_id as projectId,p.company_id as companyId,p.department_id as departmentIdCode,(SELECT name FROM dic_info WHERE code = p.department_id) as departmentId,p.`name` as `name`,p.sex as sex,p.country_code as countryCode,p.birthday as birthday,p.education_level as educationLevelCode,(SELECT name FROM dic_info WHERE code = p.education_level) AS educationLevel,p.id_card as idCard,p.photo as photo,p.political_status as politicalStatus,p.phone as phone,p.major AS majorCode,(SELECT name FROM dic_info WHERE code = p.major) AS major,p.insurance as insurance,p.duties AS dutiesCode,(SELECT name FROM dic_info WHERE code = p.duties) AS duties,p.state as state,p.certificate as certificate,p.id_card_file_id as idCardFileId,p.certificate_file_id as certificateFileId,p.social_security_file_id as socialSecurityFileId,p.labor_contract_file_id as laborContractFileId,p.diploma_file_id as diplomaFileId ");
        sql.append(" from company_person p,company_base_info c,(select @rowno:=#{page}) rn");
        sql.append(" where p.duties not in ('1010001','1010002','1010003','1010004') and p.company_id=c.id");
        sql.append(" and p.state=1 and p.project_id=0 ");
        if (StringUtils.isNotBlank(qyId)) {
            sql.append(" and c.id=#{qyId}");
        }
        if (StringUtils.isNotBlank(depId)) {
            sql.append(" and p.department_id=#{depId}");
        }
        if (StringUtils.isNotBlank(name)) {
            sql.append(" and p.name like CONCAT('%',#{name},'%')");
        }
        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());

        return sql.toString();
    }

    /**
     * 查询企业从业人员信息 条件查询 条数
     */
    public String findCYRYCount(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String qyId = (String) map.get("qyId");
        String name = (String) map.get("name");
        String depId = (String) map.get("depId");

        sql.append("select count(*) from company_person p,company_base_info c where");
        sql.append("  p.duties not in ('1010001','1010002','1010003','1010004') and p.company_id=c.id and p.state=1 and p.project_id=0 ");
        if (StringUtils.isNotBlank(qyId)) {
            sql.append(" and c.id=#{qyId}");
        }
        if (StringUtils.isNotBlank(depId)) {
            sql.append(" and p.department_id=#{depId}");
        }
        if (StringUtils.isNotBlank(name)) {
            sql.append(" and p.name like CONCAT('%',#{name},'%')");
        }

        return sql.toString();
    }

    /**
     * 条件查询企业信息
     */
    public String findCorpByCondition(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String qyName = (String) map.get("qyName");
        Integer district = (Integer) map.get("district");
        String cxDateBegin = (String) map.get("cxDateBegin");
        String cxDateEnd = (String) map.get("cxDateEnd");
        Integer state = (Integer) map.get("state");
        Page page = (Page) map.get("page");
        String area = (String) map.get("area");
        sql.append("SELECT  a.company_id as id,MAX(a.gmt_create),b.state,b.business_license_no as businessLicenseNo,b.`name`,(select `name` from district_street where id=b.street) as streetName,c.`name` as districtName,b.gmt_create as gmtCreate,b.register_address as registerAddress");
        sql.append(" from company_info_opinion a");
        sql.append(" LEFT JOIN company_base_info b ON a.company_id=b.id");
        sql.append(" LEFT JOIN district_street c ON c.id = b.district");
        sql.append(" WHERE b.state in(0,1,2,3,4)");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and b.community in (").append(area).append(")");
        }
        if (StringUtils.isNotBlank(cxDateBegin)) {
            sql.append(" and b.gmt_create>=#{cxDateBegin}");
        }
        if (StringUtils.isNotBlank(cxDateEnd)) {
            sql.append(" and b.gmt_create<=#{cxDateEnd}");
        }
        if (state != null) {
            sql.append(" and b.state=#{state}");
        }
        if (district != null) {
            sql.append(" and b.district = #{district}");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and b.`name` like CONCAT('%',#{qyName},'%')");
        }
        sql.append(" GROUP BY a.company_id");
        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 条件查询企业信息 条数
     */
    public String findCorpByConditionCount(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String qyName = (String) map.get("qyName");
        Integer district = (Integer) map.get("district");
        String cxDateBegin = (String) map.get("cxDateBegin");
        String cxDateEnd = (String) map.get("cxDateEnd");
        Integer state = (Integer) map.get("state");
        String area = (String) map.get("area");

        sql.append("SELECT COUNT(*) FROM (");
        sql.append("SELECT  a.company_id as id,MAX(a.gmt_create)");
        sql.append(" from company_info_opinion a LEFT JOIN company_base_info b ON a.company_id=b.id ");
        sql.append(" LEFT JOIN district_street c ON c.id = b.district WHERE b.state in(0,1,2,3,4) ");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and b.community in (").append(area).append(")");
        }
        if (StringUtils.isNotBlank(cxDateBegin)) {
            sql.append(" and b.gmt_create>=#{cxDateBegin}");
        }
        if (StringUtils.isNotBlank(cxDateEnd)) {
            sql.append(" and b.gmt_create<=#{cxDateEnd}");
        }
        if (state != null) {
            sql.append(" and b.state=#{state}");
        }
        if (district != null) {
            sql.append(" and b.district = #{district}");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and b.`name` like CONCAT('%',#{qyName},'%')");
        }
        sql.append(" GROUP BY a.company_id)t");
        return sql.toString();
    }

    /**
     * 从业人员查询  条件查询
     */
    public String findPersonByCondition(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String name = (String) map.get("name");
        String qyName = (String) map.get("qyName");
        Page page = (Page) map.get("page");
        String area = (String) map.get("area");

        sql.append("select @rowno:=@rowno+1 as row,c.`name` as qyName,p.id as id,DATE_FORMAT(p.gmt_create, '%Y-%m-%d') as gmtCreate,DATE_FORMAT(p.gmt_modified, '%Y-%m-%d') as gmtModified,p.person_id as personId,p.project_id as projectId,p.company_id as companyId,p.department_id as departmentIdCode,(SELECT name FROM dic_info WHERE code = p.department_id) as departmentId,p.`name` as `name`,p.sex as sex,p.country_code as countryCode,p.birthday as birthday,p.education_level as educationLevelCode,(SELECT name FROM dic_info WHERE code = p.education_level) AS educationLevel,p.id_card as idCard,p.photo as photo,p.political_status as politicalStatus,p.phone as phone,p.major AS majorCode,(SELECT name FROM dic_info WHERE code = p.major) AS major,p.insurance as insurance,p.duties AS dutiesCode,(SELECT name FROM dic_info WHERE code = p.duties) AS duties,p.state as state,p.certificate as certificate,p.id_card_file_id as idCardFileId,p.certificate_file_id as certificateFileId,p.social_security_file_id as socialSecurityFileId,p.labor_contract_file_id as laborContractFileId,p.diploma_file_id as diplomaFileId ");
        sql.append(" from company_person p,company_base_info c,(select @rowno:=#{page}) rn where");
        sql.append(" c.id=p.company_id and p.state=1 and p.project_id=0 and c.state =2 and p.duties not in('1010001','1010002','1010003','1010004')");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and c.community in (").append(area).append(")");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.name like CONCAT('%',#{qyName},'%')");
        }
        if (StringUtils.isNotBlank(name)) {
            sql.append(" and p.name like CONCAT('%',#{name},'%')");
        }
        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 从业人员查询  条件查询  条数
     */
    public String findPersonByConditionCount(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String name = (String) map.get("name");
        String qyName = (String) map.get("qyName");
        String area = (String) map.get("area");

        sql.append("select count(*) from company_person p,company_base_info c");
        sql.append(" where c.id=p.company_id and p.state=1 and p.project_id=0 and c.state = 2 and p.duties not in(1003001,1003002,1003003,1003004)");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and c.community in (").append(area).append(")");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.name like CONCAT('%',#{qyName},'%')");
        }
        if (StringUtils.isNotBlank(name)) {
            sql.append(" and p.name like CONCAT('%',#{name},'%')");
        }

        return sql.toString();
    }

    /**
     * 查询企业绩效教育情况
     */
    public String findTrain(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        Page page = (Page) map.get("page");
        Integer corpId = (Integer) map.get("corpId");
        String name = (String) map.get("name");

        sql.append("select @rowno:=@rowno+1 as row,p.`name` as `name`,p.duties AS dutiesCode,(SELECT name FROM dic_info WHERE code = p.duties) AS duties,t.id as id,DATE_FORMAT(t.gmt_create, '%Y-%m-%d') as gmtCreate,DATE_FORMAT(t.gmt_modified, '%Y-%m-%d') as gmtModified,t.person_id as personId,t.`subject` as `subject`,t.train_begin_time as trainBeginTime,t.state as state,t.train_end_time as trainEndTime,t.corp_id as corpId");
        sql.append(" from company_train t left join company_base_info c on t.corp_id=c.id");
        sql.append(" left join company_person p on p.id=t.person_id,(select @rowno:=#{page}) rn ");
        sql.append(" where t.state=2");
        sql.append(" and c.id=").append(corpId);
        if (StringUtils.isNotBlank(name)) {
            sql.append(" and p.`name` like CONCAT('%','").append(name).append("','%')");
        }

        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 查询企业绩效教育情况 条数
     */
    public String findTrainCount(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        Integer corpId = (Integer) map.get("corpId");
        String name = (String) map.get("name");

        sql.append("select count(*)");
        sql.append(" from company_train t left join company_base_info c on t.corp_id=c.id");
        sql.append(" left join company_person p on p.id=t.person_id ");
        sql.append("  where t.state =2");
        sql.append(" and c.id=").append(corpId);
        if (StringUtils.isNotBlank(name)) {
            sql.append(" and p.`name` like CONCAT('%','").append(name).append("','%')");
        }
        return sql.toString();
    }

    public String getCorpNameList(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        sql.append("select id as id,`name` as name from company_base_info where state=2 ");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and community in (").append(area).append(")");
        }
        return sql.toString();
    }

    /**
     * 查询该账号管理区域下企业的继续教育情况
     */
    public String getTrainList(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        Page page = (Page) map.get("page");
        String qyName = (String) map.get("qyName");
        Integer state = (Integer) map.get("state");
        Integer department = (Integer) map.get("department");

        sql.append("select @rowno:=@rowno+1 as row,c.`name` as qyName,p.`name` as `name`,p.duties AS dutiesCode,(SELECT name FROM dic_info WHERE code = p.duties) AS duties,t.id as id,DATE_FORMAT(t.gmt_create, '%Y-%m-%d') as gmtCreate,DATE_FORMAT(t.gmt_modified, '%Y-%m-%d') as gmtModified,t.person_id as personId,t.`subject` as `subject`,t.train_begin_time as trainBeginTime,t.state as state,t.train_end_time as trainEndTime,t.corp_id as corpId,t.department as department ");
        sql.append(" from company_train t,company_base_info c,company_person p,(select @rowno:=#{page}) rn ");
        sql.append(" where c.id=t.corp_id and t.person_id =p.id and t.state<>0");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and c.community in (").append(area).append(")");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        if (state != null) {
            sql.append(" and t.state=#{state}");
        }
        if (department != null) {
            sql.append(" and t.department=#{department}");
        }

        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 查询该账号管理区域下企业的继续教育情况 条数
     */
    public String getTrainListCount(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        String qyName = (String) map.get("qyName");
        Integer state = (Integer) map.get("state");
        Integer department = (Integer) map.get("department");
        sql.append("select count(*) ");
        sql.append(" from company_train t,company_base_info c,company_person p");
        sql.append(" where c.id=t.corp_id and t.person_id =p.id and t.state<>0");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and c.community in (").append(area).append(")");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        if (state != null) {
            sql.append(" and t.state=#{state}");
        }
        if (department != null) {
            sql.append(" and t.department=#{department}");
        }
        return sql.toString();
    }

    /**
     * 继续教育审批按钮  点击后显示的列表
     */
    public String getSPTrainList(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        Page page = (Page) map.get("page");
        String department = (String) map.get("department");

        sql.append("select @rowno:=@rowno+1 as row,c.`name` as qyName,p.`name` as `name`,p.duties AS dutiesCode,(SELECT name FROM dic_info WHERE code = p.duties) AS duties,t.id as id,DATE_FORMAT(t.gmt_create, '%Y-%m-%d') as gmtCreate,DATE_FORMAT(t.gmt_modified, '%Y-%m-%d') as gmtModified,t.person_id as personId,t.`subject` as `subject`,t.train_begin_time as trainBeginTime,t.state as state,t.train_end_time as trainEndTime,t.corp_id as corpId,t.department as department ");
        sql.append(" from company_train t,company_base_info c,company_person p,(select @rowno:=#{page}) rn ");
        sql.append(" where c.id=t.corp_id and t.person_id =p.id");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and c.community in (").append(area).append(")");
        }
        sql.append(" and t.state=1");
        sql.append(" and t.department in (").append(department).append(")");
        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 继续教育审批按钮  点击后显示的列表 条数
     */
    public String getSPTrainListCount(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        String department = (String) map.get("department");

        sql.append("select count(*) ");
        sql.append(" from company_train t,company_base_info c,company_person p ");
        sql.append(" where c.id=t.corp_id and t.person_id =p.id");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and c.community in (").append(area).append(")");
        }
        sql.append(" and t.state=1");
        sql.append(" and t.department in (#{department})");

        return sql.toString();
    }


}
